package com.bossone.finddbchange;

import com.bossone.finddbchange.entity.ColumnEntity;
import com.bossone.finddbchange.entity.TableEntity;

import java.sql.*;
import java.util.*;

/**
 * 功能
 * 更新可能因为数据表结构发生变化导致程序启动失败
 * 此程序能查找对比两个数据库的表结构以及字段的差异
 * 方便查找问题所在
 */
public class CompareDbMain {

    private static String db_url1 = "jdbc:mysql://47.106.214.166:3306/bossma_init?useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static String db_user1 = "root";
    private static String db_pwd1 = "Bx@201202";

    private static String db_url2 = "jdbc:mysql://119.23.52.46:3306/jnpf_init_356?useUnicode=true&characterEncoding=utf8&useSSL=false";
    private static String db_user2 = "root";
    private static String db_pwd2 = "175dc5c004c01ba9";

    public static void main(String[] args) throws Exception{
        System.out.println("【结果说明】");
        System.out.println("1. ++表示A比B多，--表示A比B少，**表示有变化");
        System.out.println(" -------------------------------------");

        Connection conn1 = new MysqlDBTools(db_url1, db_user1, db_pwd1).getConnection();
        Connection conn2 = new MysqlDBTools(db_url2, db_user2, db_pwd2).getConnection();

        //对比表信息
        List<TableEntity> tables1 = getTables(conn1);
        List<TableEntity> tables2 = getTables(conn2);
        StringBuffer tableRes = compareTable(tables1, tables2);
        System.out.println("【表对比结果】");
        System.out.println(conn1.getCatalog() + " vs " + conn2.getCatalog());
        System.out.println("表数量：" + tables1.size() + " vs " + tables2.size());
        System.out.println(tableRes);

        StringBuffer columnRes = compareColumn(conn1, conn2, tables1, tables2);
        System.out.println("【表字段对比结果】");
        System.out.println(columnRes);
    }


    public static StringBuffer compareColumn(Connection conn1, Connection conn2, List<TableEntity> tables1, List<TableEntity> table2){
        StringBuffer sb = new StringBuffer();
        for(TableEntity t1: tables1){
            if(tableContains(table2, t1)){
                sb.append("【").append(t1.getTableName()).append("】").append("\r\n");
                List<ColumnEntity> columnEntities1 = getColumns(conn1, t1);
                List<ColumnEntity> columnEntities2 = getColumns(conn2, t1);

                for(ColumnEntity c1: columnEntities1){
                    if(columnContains(columnEntities2, c1)){
                        ColumnEntity c2 = getColumnFromList(columnEntities2, c1);
                        if(!c2.getColumnDisplaySize().equals(c1.getColumnDisplaySize())
                                || !c2.getColumnTypeName().equals(c1.getColumnTypeName())
                                || !c2.getScale().equals(c1.getScale())){
                            sb.append("  **").append(c2.getColumnName()).append("\r\n");
                        }
                        //System.out.println("对比字段属性");
                        continue;
                    }
                }

                for(ColumnEntity c2: columnEntities2){
                    if(!columnContains(columnEntities1, c2)){
                        sb.append("  --").append(c2.getColumnName()).append("\r\n");
                        continue;
                    }
                }

                for(ColumnEntity c1: columnEntities1){
                    if(!columnContains(columnEntities2, c1)){
                        sb.append("  ++").append(c1.getColumnName()).append("\r\n");
                        continue;
                    }
                }
            }
        }
        return sb;
    }
    //对比表信息
    public static StringBuffer compareTable(List<TableEntity> tab1, List<TableEntity> tab2){
        StringBuffer sb = new StringBuffer();
        for(TableEntity t1: tab1){
            if(tableContains(tab2, t1)){
                sb.append("    ").append(t1.getTableName()).append("\r\n");
                continue;
            }
        }

        for(TableEntity t2: tab2){
            if(!tableContains(tab1, t2)){
                sb.append("  --").append(t2.getTableName()).append("\r\n");
                continue;
            }
        }

        for(TableEntity t1: tab1){
            if(!tableContains(tab2, t1)){
                sb.append("  ++").append(t1.getTableName()).append("\r\n");
                continue;
            }
        }

        return sb;
    }
    //获取字段信息
    public static List<ColumnEntity> getColumns(Connection conn, TableEntity table){
        List<ColumnEntity> columnEntities = new LinkedList<>();
        String sql = "select * from " + table.getTableName();
        try {
            PreparedStatement ps = conn.prepareStatement(sql);
            ResultSet rs = ps.executeQuery();
            ResultSetMetaData meta = rs.getMetaData();
            int columnCount = meta.getColumnCount();
            for (int i = 1; i < columnCount + 1; i++) {
                ColumnEntity columnEntity = new ColumnEntity();
                columnEntity.setColumnName(meta.getColumnName(i).toLowerCase());
                columnEntity.setColumnTypeName( meta.getColumnTypeName(i).toLowerCase());
                columnEntity.setColumnDisplaySize(meta.getColumnDisplaySize(i));
                columnEntity.setScale(meta.getScale(i));
                columnEntity.setTableName(meta.getTableName(i).toLowerCase());

                columnEntities.add(columnEntity);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return columnEntities;
    }

    //获取表信息
    public static List<TableEntity> getTables(Connection conn){
        List<TableEntity> tableEntities = new LinkedList<>();
        try {
            DatabaseMetaData dbMetaData = conn.getMetaData();
            ResultSet rs = dbMetaData.getTables(conn.getCatalog(), null, null,new String[] { "TABLE" });
            while (rs.next()) {// ///TABLE_TYPE/REMARKS
                TableEntity table = new TableEntity();
                table.setTableName(rs.getString("TABLE_NAME").toLowerCase());
                table.setTableType(rs.getString("TABLE_TYPE").toLowerCase());
                table.setTableCat(rs.getString("TABLE_CAT").toLowerCase());
                table.setTableSchem(rs.getString("TABLE_SCHEM")==null? "": rs.getString("TABLE_SCHEM").toLowerCase());
                table.setRemarks(rs.getString("REMARKS").toLowerCase());
                tableEntities.add(table);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return tableEntities;
    }

    //从list里面获取
    public static ColumnEntity getColumnFromList(List<ColumnEntity> columnEntities, ColumnEntity column){
        for(ColumnEntity c: columnEntities){
            if(c.getColumnName().equals(column.getColumnName())
                    && c.getTableName().equals(column.getTableName())){
                return c;
            }
        }

        return null;
    }

    public static boolean tableContains(List<TableEntity> tableEntities, TableEntity table){
        for(TableEntity tab: tableEntities){
            if(tab.getTableName().equals(table.getTableName())){
                return true;
            }
        }

        return  false;
    }

    public static boolean columnContains(List<ColumnEntity> columnEntities, ColumnEntity column){
        for(ColumnEntity tab: columnEntities){
            if(tab.getColumnName().equals(column.getColumnName())
                    && tab.getTableName().equals(column.getTableName())){
                return true;
            }
        }

        return false;
    }
}